/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import edu.cmu.lti.sepia.data.resource.ResourceManager;
import edu.cmu.lti.sepia.util.ExceptionUtil;

/**
 * 
 * Responsible for making connection with mysql db.
 * 
 * @author Hideki Shima
 *
 */
public class SQL {

	private final static String driver = "com.mysql.jdbc.Driver";
	
	private final static Logger logger = Logger.getLogger(SQL.class);

	private Connection connection; // order matters
	
	public SQL() {
		try {
			createSQLConnection();
		} catch ( SQLException e ) {
			logger.error( ExceptionUtil.exceptionToString(e) );			
		}
	}
	
	public Connection getConnection() {
		return connection;
	}
	
	private void createSQLConnection() throws SQLException {
	  if (ResourceManager.dbHost==null) {
	    ResourceManager.init(); 
	  }
	  
		String host = ResourceManager.dbHost;
		String port = ResourceManager.dbPort;
		String username = ResourceManager.dbUser;
		String password = ResourceManager.dbPass;
		
		// Classload the driver
		try {
			Class.forName( driver );
		} catch ( ClassNotFoundException e ) {
			logger.error( ExceptionUtil.exceptionToString(e) );			
		}
		
		String url = "jdbc:mysql://"+host+":"+port+"/"
			+ "?user="+username
			+ "&password="+password
			+ "&useUnicode=true"
			+ "&characterEncoding=UTF-8";
		
		// Open cannection
		if (connection==null || connection.isClosed() ) {
			connection = DriverManager.getConnection( url );
		}
		logger.trace( "Created connection "+connection.hashCode() );
	}
	
	public void close() throws SQLException {
		try {
			if ( connection != null && !connection.isClosed() ) {
				connection.close();
				logger.trace( "Closed connection "+connection.hashCode() );
			}
		} catch ( SQLException e ) {
			String error = ExceptionUtil.exceptionToString(e);
			logger.error( "Failed to close a connection.\n"+error );
		}
	}
	
	public String execute( String sql ) {
		StringBuilder sb = new StringBuilder();
		PreparedStatement ps = null;
		
		try {
			ps = connection.prepareStatement( sql );
			logger.trace( sql );
			ps.execute();
		} catch (SQLException e) {
			String error = ExceptionUtil.exceptionToString(e);
			logger.error( sql+"\n"+error );
			sb.append( error );
		} finally {
			try {
				ps.close();
			} catch (Exception e2) {
				
			}
		}
		return sb.toString();
	}
	
	public void execute( PreparedStatement ps ) throws SQLException {
		logger.info( getCommand(ps) );
		ps.execute();
	}
	
	public ResultSet executeSelect( PreparedStatement statement ) {
		ResultSet rs = null;
		try {
			logger.trace( getCommand(statement) );
			rs = statement.executeQuery();
		} catch (SQLException e) {
			logger.error( getCommand(statement)+"\n"+ExceptionUtil.exceptionToString(e) );
		} finally {
			
		}
		return rs;
	}
	
	public String executeInSilence( PreparedStatement statement ) {
		try {
			logger.trace( getCommand(statement) );
			statement.execute();
			return "";
		} catch (SQLException e) {
			String error = ExceptionUtil.exceptionToString(e);
			logger.error( getCommand(statement)+"\n"+error);
			return error;
		} finally {

		}
	}
	
	public void executeBatch( PreparedStatement statement ) {
		
		try {
			logger.trace( getCommand(statement) );
			connection.setAutoCommit(false);
			statement.executeBatch();
			connection.setAutoCommit(true);
		} catch (SQLException e) {
			logger.error( getCommand(statement)+"\n"+ExceptionUtil.exceptionToString(e) );
		} finally {

		}
	}
		
	private String getCommand( PreparedStatement ps ) {
		return ps.toString().replaceAll(".+: ", "");
	}
	
	@Override
	protected void finalize() throws Throwable {
		close();
	}
}


